<?php

class Application_Model_Users extends Zend_Db_Table_Abstract
{
    protected $_name = 'users';
    protected $_primary = 'id';

    public function createUser($params)
    {
         $data = array(
         	'username' => $params['username'],
            'password' => md5($params['password']),
            'first_name' => $params['first_name'],
            'last_name' => $params['last_name'],
            'position' => $params['position']
        );

        $this->insert($data);
    }


	public function editUser($params)
    {
        $data = array(
            'password' => md5($params['password']),
            'first_name' => $params['first_name'],
            'last_name' => $params['last_name'],
            'position' => $params['position']
        );
        $where = $this->getAdapter()->quoteInto('id = ?', $params['id']);

        $this->update($data, $where);
    }

    public function getUser($id)
    {
   		$where = $this->getAdapter()->quoteInto('id = ?', $id);
        $row = $this->fetchRow($where);

        if ($row) {
            return $row->toArray();
        } else {
            throw new Exception('User not found');
        }
    }

    public function getUsersByIds(array $ids)
    {
        return $this->find($ids)->toArray();
    }

    public function getUserList()
    {
        return $this->fetchAll()->toArray();
    }

    public function getUserListSimple()
    {
        $data = $this->getUserList();
        $out = array();

        foreach ($data as $row) {
            $out[$row['id']] = $row['first_name'] . ' ' . $row['last_name'];
        }

        return $out;
    }

    public function deleteUser($id)
    {
        $this->getAdapter()->beginTransaction();
        try {
            $this->getAdapter()->query('
                DELETE FROM logs
                WHERE user_id = ?
            ', intval($id));

             $this->getAdapter()->query('
                DELETE FROM tickets
                WHERE user_id = ?
            ', intval($id));

             $st = $this->getAdapter()->query('
                SELECT id FROM tasks
                WHERE supervisor = ?
            ', intval($id));

            $arrTasks = $st->fetchAll();
            foreach($arrTasks as $task) {
                $st = $this->getAdapter()->query('
                DELETE FROM tickets
                WHERE task_id = ?
            ', intval($task["id"]));
            }

            $this->getAdapter()->query('
                DELETE FROM tasks
                WHERE supervisor = ?
             ', intval($id));

            $this->getAdapter()->query('
                DELETE FROM users
                WHERE id = ?
             ', intval($id));

            $this->getAdapter()->commit();
        } catch(Exception $e) {
            $this->getAdapter()->rollBack();
            print_r($e);
        }

    }

    public function getUserTasks($id)
    {
        $st = $this->getAdapter()->query('
            SELECT a.id, a.name, SUM(t.hours) AS hours
            FROM tickets t
            JOIN users u ON u.id = t.user_id
            JOIN tasks a ON a.id = t.task_id
            WHERE t.user_id = ?
            GROUP BY a.id
            ORDER BY a.name
        ', intval($id));

        return $st->fetchAll();
    }

    /*public function getUsersByTask($task_id)
    {
        $tickets = new Application_Model_Tickets();
        $ticketsList = $tickets->getTicketListByTask($task_id);

        $ids = array();
        foreach ($ticketsList as $t) {
            $ids[] = $t['user_id'];
        }

        return $this->getUsersByIds($ids);
    }*/

	public function setLastAction($params)
    {
    	 $data = array(
         	'log_id' => $params["log_id"]
        );

    	$where = $this->getAdapter()->quoteInto('id = ?', $params["user_id"]);
        $this->update($data, $where);
    }
}

